package cn.pconline.autoclub.task;

import cn.pconline.autoclub.task.util.McUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * （实时）单个车友会下的周排行和总排行相关任务
 *
 * @author chensy
 */
public class MemberRank {

    private static Logger log = LoggerFactory.getLogger(MemberRank.class);
    private String allClubIdSql = "SELECT club_id FROM ac_club";	//	查询所有车友会id
    private String allUserIdSql = "SELECT user_id FROM ac_member_info where club_id = ?"; //车友会下的所有会员
    //总排行榜
    private String deleteMemberAllRankSql = "delete from ac_member_all";  //清空所有车友会会员总排行
    private String deleteOneMemberAllRankSql = "delete from ac_member_all where club_id = ? and user_id = ?";  //清空单个会员总排行
    private String allUserCheckInSql = "SELECT total_checkins FROM  ac_member_checkin where club_id = ? and user_id= ? "; //查询车友会成员签到 TODO 这个签到数还包括重复签到的
    private String allUserPetrol = "select petrol from  ac_member_info where club_id = ? and user_id = ?";//查用户当前总油值(不需要重新计算)
    private String allTopicSql = "select count(1) num from ac_club_dyna where club_id = ? and create_by= ? and type in (1,4)";   //个人总帖子数
    private String allPickSql = "select count(1) num from ac_club_dyna where club_id = ? and create_by= ? and pick > 0 and type in (1,4)"; //个人精华帖子数
    private String allRecommendSql = "select count(1) num from ac_club_dyna where club_id = ? and create_by= ? and recommend = 1 and type in (1,4)"; //个人推荐帖子数
    private String addMemberRank = "insert into ac_member_all"
            + " (club_id,user_id,check_count,topic_count,pick_count,recommend_count,petrol,create_at) VALUES (?,?,?,?,?,?,?,now())"
            + " ON DUPLICATE KEY UPDATE  check_count=check_count+?,topic_count=topic_count+?,pick_count=pick_count+?,recommend_count=recommend_count+?,"
            + " petrol=petrol+?,update_at = now()";   //添加到总排行榜
    //周排行榜
    private String deleteMemberWeekRankSql = "delete from ac_member_week"; //清空所有车友会周排行
    private String deleteOneMemberWeekRankSql = "delete from ac_member_week where club_id = ? and user_id = ?"; //清空单个车友会周排行
    private String weekCheckInSql = "SELECT count(1) checkNum,substr(create_at,1,10) createYmd from ac_member_checkin_record where club_id = ? and user_id= ? and create_at > ? and create_at <= ? group by user_id,club_id,substr(create_at,1,10)";
    private String weekMemberDailyDetail = "select sum(petrol) petrol "
            + "from ac_member_petrol_detail where club_id = ? and user_id = ? and create_at > ? and create_at <= ?"; //查询每天车友会成员详细表
    private String weekTopicSql = "select count(1) num from ac_club_dyna where club_id = ? and create_by= ? and create_date > ? and create_date <= ? and type in (1,4)"; //个人周总帖子
    private String weekPickSql = "select count(1) num from ac_club_dyna where club_id = ? and create_by= ? and create_date > ? and pick > 0 and create_date <= ? and type in (1,4)";//个人周精华帖子
    private String weekRecommendql = "select count(1) num from ac_club_dyna where club_id = ? and create_by= ? and create_date > ? and recommend = 1 and create_date <= ? and type in (1,4)";//个人周精华帖子
    private String weekMemberRank = "insert into ac_member_week"
            + " (club_id,user_id,check_count,topic_count,pick_count,recommend_count,petrol,create_at) VALUES (?,?,?,?,?,?,?,now())"
            + "  ON DUPLICATE KEY UPDATE check_count=check_count+?,topic_count=topic_count+?,pick_count=pick_count+?,recommend_count=recommend_count+?,"
            + " petrol=petrol+?,update_at = now()"; //添加到周排行榜
    //每周清排行榜
    private String weekClearRank = "update ac_member_week set rank = 0";//清上周排名
    private String weekCountRank = "select mweek.user_id from ac_member_info info left join ac_member_week mweek on info.user_id= mweek.user_id and info.club_id= mweek.club_id"
            + " where mweek.club_id = ? order by mweek.petrol desc , mweek.rank,info.join_at "; //查询所有周排行榜排名
    private String updateWeekRank = "update ac_member_week set rank = ?,check_count =0,topic_count=0,pick_count=0,recommend_count=0,petrol=0 where club_id =? and user_id = ? ";//设置本周排名
    private String updateRankYesterdayBefore = "update ac_member_week set rank_yesterday_before = rank_yesterday, rank_yesterday = ?, petrol_yesterday = petrol  where club_id =? and user_id = ? ";//更新昨天，前天排名

    public static void main(String[] args) {
        AppUtils.initLog(MemberRank.class);;
        if (args == null || args.length < 3) {
            System.out.println("please input 3 args[initOneMemberAllRank clubId userId |initOneMemberWeekRank clubId userId ]");
            return;
        }
        String type = args[0];
        long clubId = Long.parseLong(args[1]);
        long userId = Long.parseLong(args[2]);
        MemberRank task = new MemberRank();
        if ("initOneMemberAllRank".equals(type)) {
            task.initOneMemberAllRank(clubId, userId);
        } else if ("initOneMemberWeekRank".equals(type)) {
            task.initOneMemberWeekRank(clubId, userId);
        } else {
            System.out.println("input error!");
        }
    }

    /**
     * 单独初始化一个会员的总排行
     *
     * @param clubId
     * @param userId
     */
    public void initOneMemberAllRank(long clubId, long userId) {
        Connection conn = DBUtils.getConnection();
        boolean oldCommit = DBUtils.getAutoCommitValue(conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn.setAutoCommit(false);
            //先删除
            ps = conn.prepareStatement(deleteOneMemberAllRankSql);
            ps.setLong(1, clubId);
            ps.setLong(2, userId);
            ps.executeUpdate();
            ps.close();

            //再更新
            ps = conn.prepareStatement(addMemberRank);
            //查询统计一个会员的数据
            Map<String, Long> map = statOneAllMember(clubId, userId, conn);
            long topic = map.get("topic");
            long pick = map.get("pick");
            long recommend = map.get("recommend");
            long petrol = map.get("petrol");
            long checkInCount = map.get("checkInCount");
            // 添加到总排行榜表
            ps.setLong(1, clubId);
            ps.setLong(2, userId);
            ps.setLong(3, checkInCount);
            ps.setLong(4, topic);
            ps.setLong(5, pick);
            ps.setLong(6, recommend);
            ps.setLong(7, petrol);
            ps.setLong(8, checkInCount);
            ps.setLong(9, topic);
            ps.setLong(10, pick);
            ps.setLong(11, recommend);
            ps.setLong(12, petrol);
            ps.executeUpdate();
            ps.close();
            conn.commit();
            //清MC缓存
            McUtils.delete("MemberAll-" + clubId + "-" + userId);
        } catch (Exception e) {
            log.error("init initOneMemberRank throws Exception", e);
        } finally {
            try {
                conn.setAutoCommit(oldCommit);
                DBUtils.releaseConn(conn, ps, rs);
            } catch (SQLException e) {
                log.error("initOneMemberRank db release throws Exception", e);
            }
        }
    }

    /**
     * 初始化各车友会成员排名，只在第一次使用（当大部分数据有问题时可以重新跑，执行时间比较久）
     *
     * @return: boolean
     */
    public void initMemberAllRank() {
        int count = 0;
        Connection conn = DBUtils.getConnection();
        boolean oldCommit = DBUtils.getAutoCommitValue(conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(deleteMemberAllRankSql);
            ps.execute();
            ps.close();

            //查询所有车友会ID
            ps = conn.prepareStatement(allClubIdSql);
            List<Long> clubIdList = new ArrayList<Long>(1024);
            rs = ps.executeQuery();
            while (rs.next()) {
                clubIdList.add(rs.getLong("club_id"));
            }
            rs.close();
            ps.close();

            if (!clubIdList.isEmpty()) {
                int countClub = 0;
                for (Long clubId : clubIdList) {
                    List<Long> uids = new ArrayList();
                    ps = conn.prepareStatement(allUserIdSql);
                    ps.setLong(1, clubId);
                    rs = ps.executeQuery();
                    PreparedStatement addStatement = conn.prepareStatement(addMemberRank);
                    while (rs.next()) {
                        long userId = rs.getLong("user_id");
                        uids.add(userId);
                        //查询统计一个会员的数据
                        Map<String, Long> map = statOneAllMember(clubId, userId, conn);
                        long topic = map.get("topic");
                        long pick = map.get("pick");
                        long recommend = map.get("recommend");
                        long petrol = map.get("petrol");
                        long checkInCount = map.get("checkInCount");
                        // 添加到总排行榜表
                        addStatement.setLong(1, clubId);
                        addStatement.setLong(2, userId);
                        addStatement.setLong(3, checkInCount);
                        addStatement.setLong(4, topic);
                        addStatement.setLong(5, pick);
                        addStatement.setLong(6, recommend);
                        addStatement.setLong(7, petrol);
                        addStatement.setLong(8, checkInCount);
                        addStatement.setLong(9, topic);
                        addStatement.setLong(10, pick);
                        addStatement.setLong(11, recommend);
                        addStatement.setLong(12, petrol);
                        addStatement.addBatch();
                        count++;
                    }
                    //批量更新一个车友会下的所有会员
                    addStatement.executeBatch();
                    addStatement.close();
                    conn.commit();
                    //做完一个车友会清一次缓存，减少数据不一致的情况（由于执行过程中没清缓存可能造成数据不一致问题的）
                    for (long uid : uids) {
                        McUtils.delete("MemberAll-" + clubId + "-" + uid);
                    }
                    countClub++;
                    log.info("the {}th club[] allRank have been init!", countClub, clubId);
                }
                log.info("{} clubUsers allRank have been init!", count);
            }
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                log.error("rollback exception", e1);
            }
            log.error("init memberAllRank throws Exception", e);
        } finally {
            try {
                conn.setAutoCommit(oldCommit);
                DBUtils.releaseConn(conn, ps, rs);
            } catch (SQLException e) {
                log.error("memberAllRank db release throws Exception", e);
            }
        }
    }

    /**
     * 单个会员总数据
     *
     * @param clubId
     * @param userId
     * @param conn
     * @return
     */
    private Map<String, Long> statOneAllMember(long clubId, long userId, Connection conn) {
        Map<String, Long> map = new HashMap<String, Long>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //查询车友会 用户的签到记录
            ps = conn.prepareStatement(DBUtils.getSql(allUserCheckInSql, "ac_member_checkin", clubId));
            ps.setLong(1, clubId);
            ps.setLong(2, userId);
            rs = ps.executeQuery();
            long checkInCount = 0;
            if (rs.next()) {
                checkInCount = rs.getLong("total_checkins");
            }
            //查询用户油值
            ps = conn.prepareStatement(allUserPetrol);
            ps.setLong(1, clubId);
            ps.setLong(2, userId);
            rs = ps.executeQuery();
            long petrol = 0;
            if (rs.next()) {
                petrol = rs.getLong("petrol");
            }
            //全部帖子数
            ps = conn.prepareStatement(DBUtils.getSql(allTopicSql, "ac_club_dyna", clubId));
            ps.setLong(1, clubId);
            ps.setLong(2, userId);
            rs = ps.executeQuery();
            long topic = 0;
            if (rs.next()) {
                topic = rs.getLong("num");
            }
            //全部精华数
            ps = conn.prepareStatement(DBUtils.getSql(allPickSql, "ac_club_dyna", clubId));
            ps.setLong(1, clubId);
            ps.setLong(2, userId);
            rs = ps.executeQuery();
            long pick = 0;
            if (rs.next()) {
                pick = rs.getLong("num");
            }
            //全部推荐数
            ps = conn.prepareStatement(DBUtils.getSql(allRecommendSql, "ac_club_dyna", clubId));
            ps.setLong(1, clubId);
            ps.setLong(2, userId);
            rs = ps.executeQuery();
            long recommend = 0;
            if (rs.next()) {
                recommend = rs.getLong("num");
            }
            map.put("topic", topic);
            map.put("pick", pick);
            map.put("recommend", recommend);
            map.put("petrol", petrol);
            map.put("checkInCount", checkInCount);
            log.info("one member has been stat ---clubId:{},userId:{},topic:{},pick:{},recommend:{},petrol:{},checkInCount:{}",
                    clubId, userId, topic, pick, recommend, petrol, checkInCount);
        } catch (SQLException e) {
            log.error("memberAllRank statOneMember throws Exception", e);
        } finally {
            DBUtils.releaseConn(null, ps, rs);
        }
        return map;
    }

    /**
     * 单个会员周数据
     *
     * @param clubId
     * @param userId
     * @param conn
     * @param weekStart
     * @param cur
     * @param sqlList
     * @return
     */
    private Map<String, Long> statOneWeekMember(long clubId, long userId, Connection conn, Date weekStart, Date cur) {
        Map<String, Long> map = new HashMap<String, Long>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //查询车友会 用户的签到记录
            long checkInCount = getOneWeekCheckInCount(clubId, userId, conn, weekStart, cur);
            //查询用户周油值
            long petrol = getOneWeekPetrol(clubId, userId, conn, weekStart, cur);
            //周全部帖子数
            ps = conn.prepareStatement(DBUtils.getSql(weekTopicSql, "ac_club_dyna", clubId));
            ps.setLong(1, clubId);
            ps.setLong(2, userId);
            ps.setTimestamp(3, new Timestamp(weekStart.getTime()));
            ps.setTimestamp(4, new Timestamp(cur.getTime()));
            rs = ps.executeQuery();
            
            long topic = 0;
            if (rs.next()) {
                System.out.println("topic num: " + rs.getLong(1));
                topic = rs.getLong("num");
            }
            rs.close();
            ps.close();

            //周全部精华数
            ps = conn.prepareStatement(DBUtils.getSql(weekPickSql, "ac_club_dyna", clubId));
            ps.setLong(1, clubId);
            ps.setLong(2, userId);
            ps.setTimestamp(3, new Timestamp(weekStart.getTime()));
            ps.setTimestamp(4, new Timestamp(cur.getTime()));
            rs = ps.executeQuery();
            long pick = 0;
            if (rs.next()) {
                pick = rs.getLong("num");
            }
            rs.close();
            ps.close();

            //周全部推荐数
            ps = conn.prepareStatement(DBUtils.getSql(weekRecommendql, "ac_club_dyna", clubId));
            ps.setLong(1, clubId);
            ps.setLong(2, userId);
            ps.setTimestamp(3, new Timestamp(weekStart.getTime()));
            ps.setTimestamp(4, new Timestamp(cur.getTime()));
            rs = ps.executeQuery();
            long recommend = 0;
            if (rs.next()) {
                recommend = rs.getLong("num");
            }
            rs.close();
            ps.close();

            map.put("topic", topic);
            map.put("pick", pick);
            map.put("recommend", recommend);
            map.put("petrol", petrol);
            map.put("checkInCount", checkInCount);
            log.info("one member has been stat ---clubId:{},userId:{},topic:{},pick:{},recommend:{},petrol:{},checkInCount:{},weekStart:{},cur:{}",
                    clubId, userId, topic, pick, recommend, petrol, checkInCount, weekStart, cur);
        } catch (SQLException e) {
            log.error("memberAllRank statOneMember throws Exception", e);
        } finally {
            DBUtils.releaseConn(null, ps, rs);
        }
        return map;
    }

    /**
     * 获取个人周油值
     *
     * @param clubId
     * @param userId
     * @param conn
     * @param weekStart
     * @param cur
     * @param dailysSql
     * @return
     */
    private long getOneWeekPetrol(long clubId, long userId, Connection conn, Date weekStart, Date cur) {
        long petrol = 0;
        try {
            List<String> dailysSql = getWeekDailySql(weekStart, cur);
            for (int i = 0; i < dailysSql.size(); i++) {
                PreparedStatement ps = conn.prepareStatement(dailysSql.get(i));
                ps.setLong(1, clubId);
                ps.setLong(2, userId);
                ps.setTimestamp(3, new Timestamp(weekStart.getTime()));
                ps.setTimestamp(4, new Timestamp(cur.getTime()));
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    petrol += rs.getLong("petrol");
                }
                rs.close();
                ps.close();
            }
        } catch (SQLException e) {
            log.error("memberWeekRank getOneWeekPetrol throws Exception", e);
        }
        return petrol > 0 ? petrol : 0;
    }

    /**
     * 获得个人周签到数量（需要去重，可能会有重复签到的记录）
     *
     * @param clubId
     * @param userId
     * @param conn
     * @param weekStart
     * @param cur
     * @return
     */
    private long getOneWeekCheckInCount(long clubId, long userId, Connection conn, Date weekStart, Date cur) {
        int checkInCount = 0;
        try {
            String curMonth = new SimpleDateFormat("_yyyyMM").format(cur);
            String weekStartMonth = new SimpleDateFormat("_yyyyMM").format(weekStart);
            //查当前月
            String curcheckinRecordSql = DBUtils.getSql(weekCheckInSql, "ac_member_checkin_record", cur);
            PreparedStatement ps = conn.prepareStatement(curcheckinRecordSql);
            ps.setLong(1, clubId);
            ps.setLong(2, userId);
            ps.setTimestamp(3, new Timestamp(weekStart.getTime()));
            ps.setTimestamp(4, new Timestamp(cur.getTime()));
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int checkNum = rs.getInt("checkNum");
                if (checkNum > 1) {
                    log.error("{},{}这一天[{}]重复签到，共签到了{}次", clubId,userId,rs.getString("createYmd"), checkNum);
                }
                checkInCount++;
            }
            rs.close();
            ps.close();
            if (!curMonth.equals(weekStartMonth)) {//跨月，查不同表
                String weekStarcheckinRecordSql = DBUtils.getSql(weekCheckInSql, "ac_member_checkin_record", weekStart);
                ps = conn.prepareStatement(weekStarcheckinRecordSql);
                ps.setLong(1, clubId);
                ps.setLong(2, userId);
                ps.setTimestamp(3, new Timestamp(weekStart.getTime()));
                ps.setTimestamp(4, new Timestamp(cur.getTime()));
                rs = ps.executeQuery();
                while (rs.next()) {
                    int checkNum = rs.getInt("checkNum");
                    if (checkNum > 1) {
                        log.error("这一天[{}]重复签到，共签到了{}次", rs.getString("createYmd"), checkNum);
                        log.error("");
                    }
                    checkInCount++;
                }
                rs.close();
                ps.close();
            }
        } catch (SQLException e) {
            log.error("memberWeekRank getOneWeekCheckInCount throws Exception", e);
        }
        return checkInCount;
    }

    /**
     * 单独初始化一个会员的周排行
     *
     * @param clubId
     * @param userId
     */
    public void initOneMemberWeekRank(long clubId, long userId) {
        Connection conn = DBUtils.getConnection();
        boolean oldCommit = DBUtils.getAutoCommitValue(conn);
        Date cur = new Date();
        Date weekStart = DateUtils.getLastWeekEnd(cur);
        PreparedStatement ps = null;
        try {
            conn.setAutoCommit(false);
            //清排行记录
            ps = conn.prepareStatement(deleteOneMemberWeekRankSql);
            ps.setLong(1, clubId);
            ps.setLong(2, userId);
            ps.execute();
            ps.close();

            //查询一个用户的数据
            Map<String, Long> map = statOneWeekMember(clubId, userId, conn, weekStart, cur);
            long topic = map.get("topic");
            long pick = map.get("pick");
            long recommend = map.get("recommend");
            long petrol = map.get("petrol");
            long checkInCount = map.get("checkInCount");
            ps = conn.prepareStatement(weekMemberRank);

            // 添加到周排行榜表
            ps.setLong(1, clubId);
            ps.setLong(2, userId);
            ps.setLong(3, checkInCount);
            ps.setLong(4, topic);
            ps.setLong(5, pick);
            ps.setLong(6, recommend);
            ps.setLong(7, petrol);
            ps.setLong(8, checkInCount);
            ps.setLong(9, topic);
            ps.setLong(10, pick);
            ps.setLong(11, recommend);
            ps.setLong(12, petrol);
            ps.executeUpdate();
            ps.close();
            conn.commit();
            //清缓存
            McUtils.delete("MemberWeek-" + clubId + "-" + userId);
        } catch (Exception e) {
            log.error("init initOneMemberWeekRank throws Exception", e);
        } finally {
            try {
                conn.setAutoCommit(oldCommit);
                DBUtils.releaseConn(conn, ps, null);
            } catch (SQLException e) {
                log.error("initOneMemberRank db release throws Exception", e);
            }
        }
    }

    /**
     * 初始化全部车友会成员周排行（当大部分数据有问题时可以重新跑，执行时间比较久）
     *
     */
    public void initMemberWeekRank(Date cur) {
        int count = 0;
        int countClub = 0;
        Date weekStart = DateUtils.getLastWeekEnd(cur);
        Connection conn = DBUtils.getConnection();
        boolean oldCommit = DBUtils.getAutoCommitValue(conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn.setAutoCommit(false);
            //清空周排行榜
            ps = conn.prepareStatement(deleteMemberWeekRankSql);
            ps.execute();
            ps.close();

            //查询所有车友会ID
            ps = conn.prepareStatement(allClubIdSql);
            List<Long> clubIdList = new ArrayList<Long>(1024);
            rs = ps.executeQuery();
            while (rs.next()) {
                clubIdList.add(rs.getLong("club_id"));
            }
            rs.close();
            ps.close();
            //逐个车友会统计周排行
            for (Long clubId : clubIdList) {
                PreparedStatement addStatement = conn.prepareStatement(weekMemberRank);

                //查该车友会下的所有会员
                List<Long> uids = new ArrayList();
                ps = conn.prepareStatement(allUserIdSql);
                ps.setLong(1, clubId);
                rs = ps.executeQuery();
                while (rs.next()) {
                    long userId = rs.getLong("user_id");
                    uids.add(userId);
                    //查询一个用户的数据
                    Map<String, Long> map = statOneWeekMember(clubId, userId, conn, weekStart, cur);
                    long topic = map.get("topic");
                    long pick = map.get("pick");
                    long recommend = map.get("recommend");
                    long petrol = map.get("petrol");
                    long checkInCount = map.get("checkInCount");
                    // 添加到周排行榜表
                    addStatement.setLong(1, clubId);
                    addStatement.setLong(2, userId);
                    addStatement.setLong(3, checkInCount);
                    addStatement.setLong(4, topic);
                    addStatement.setLong(5, pick);
                    addStatement.setLong(6, recommend);
                    addStatement.setLong(7, petrol);
                    addStatement.setLong(8, checkInCount);
                    addStatement.setLong(9, topic);
                    addStatement.setLong(10, pick);
                    addStatement.setLong(11, recommend);
                    addStatement.setLong(12, petrol);
                    addStatement.addBatch();
                    count++;
                }
                rs.close();
                ps.close();
                //批量更新一个车友会下的所有会员
                addStatement.executeBatch();
                addStatement.close();
                conn.commit();
                //做完一个车友会清一次缓存，减少数据不一致的情况（由于执行过程中没清缓存可能造成数据不一致问题的）
                for (long uid : uids) {
                    McUtils.delete("MemberWeek-" + clubId + "-" + uid);
                }
                countClub++;
                log.info("the {}th club({}) has stat", countClub, clubId);
            }
            log.info("{} clubUsers weekRank have been init!", count);
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                log.error("rollback exception", ex);
            }
            log.error("init memberWeekRank throws Exception", e);
        } finally {
            try {
                conn.setAutoCommit(oldCommit);
                DBUtils.releaseConn(conn, ps, rs);
            } catch (SQLException e) {
                log.error("initMemberWeekRank releaseConn thorws", e);
            }
        }
    }

    /**
     *
     * 查找统计周排行需要查找的油值日志表（如果有重复签到，这里统计到的油值是会有多的）
     *
     * @param weekStart 上周开始时间
     * @param cur	当前时间
     */
    private List<String> getWeekDailySql(Date weekStart, Date cur) {
        List<String> sqlList = new ArrayList<String>();
        String curSql = DBUtils.getSql(weekMemberDailyDetail, "ac_member_petrol_detail", cur);
        String weekStartSql = DBUtils.getSql(weekMemberDailyDetail, "ac_member_petrol_detail", weekStart);
        sqlList.add(curSql);
        if (!curSql.equals(weekStartSql)) {
            sqlList.add(weekStartSql);
        }
        return sqlList;
    }

    /**
     *
     * 每周周排行榜清理（数据会清0，所以周一凌晨执行）
     *
     * @return
     */
    public void clearWeekRank() {
        Connection conn = DBUtils.getConnection();
        boolean oldCommit = DBUtils.getAutoCommitValue(conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn.setAutoCommit(false);
            //清除上周所有排名
            ps = conn.prepareStatement(weekClearRank);
            ps.execute();
            ps.close();

            //查询所有车友会
            ps = conn.prepareStatement(allClubIdSql);
            List<Long> clubIdList = new ArrayList<Long>(1024);
            rs = ps.executeQuery();
            while (rs.next()) {
                clubIdList.add(rs.getLong("club_id"));
            }
            rs.close();
            ps.close();
            int countClub = 0;
            //针对每个车友会进行排名
            for (Long clubId : clubIdList) {
                int count = 0;
                ps = conn.prepareStatement(weekCountRank);
                ps.setLong(1, clubId);
                rs = ps.executeQuery();
                while (rs.next()) {
                    count++;
                    long userId = rs.getLong("user_id");
                    ps = conn.prepareStatement(updateWeekRank);//算下本周排名，记录下来
                    ps.setInt(1, count);
                    ps.setLong(2, clubId);
                    ps.setLong(3, userId);
                    ps.execute();
                    ps.close();
                    conn.commit();//更新一条提交一次
                    //清MC缓存
                    McUtils.delete("MemberWeek-" + clubId + "-" + userId);
                }
                rs.close();
                ps.close();
                countClub++;
                log.info("the {}th club[{}] weekRank have been clean!", countClub, clubId);
            }
            log.info("club weekRank have been clean!");
        } catch (Exception e) {
            log.error("clearWeekRank throws Exception", e);
        } finally {
            try {
                conn.setAutoCommit(oldCommit);
                DBUtils.releaseConn(conn, ps, rs);
            } catch (SQLException e) {
                log.error("clearWeekRank releaseConn thorws", e);
            }
        }
    }
    
    /**
     * 每天更新会员昨天排名，前天排名，前天周油值
     * 1.按最新周油值进行排列
     * 2.将昨天排名复制到前天排名
     * 3.更新昨天排名
     */
    public void updateRankYesterdayAndBefore() {
    	Connection conn = DBUtils.getConnection();
        boolean oldCommit = DBUtils.getAutoCommitValue(conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn.setAutoCommit(false);

            //查询所有车友会
            ps = conn.prepareStatement(allClubIdSql);
            List<Long> clubIdList = new ArrayList<Long>(1024);
            rs = ps.executeQuery();
            while (rs.next()) {
                clubIdList.add(rs.getLong("club_id"));
            }
            rs.close();
            ps.close();
            int countClub = 0;
            //针对每个车友会进行排名
            for (Long clubId : clubIdList) {
                int count = 0;
                ps = conn.prepareStatement(weekCountRank);
                ps.setLong(1, clubId);
                rs = ps.executeQuery();
                while (rs.next()) {
                    count++;
                    long userId = rs.getLong("user_id");
                    ps = conn.prepareStatement(updateRankYesterdayBefore);//更新昨天，前天排名,昨天、前天油值
                    ps.setInt(1, count);
                    ps.setLong(2, clubId);
                    ps.setLong(3, userId);
                    ps.execute();
                    ps.close();
                    conn.commit();//更新一条提交一次
                    //清MC缓存
                    McUtils.delete("MemberWeek-" + clubId + "-" + userId);
                }
                rs.close();
                ps.close();
                countClub++;
                log.info("the {}th club[{}] yandYesterday have been updated!", countClub, clubId);
            }
            log.info("club yandYesterday have been updated!");
        } catch (Exception e) {
            log.error("updateRankYesterdayAndBefore throws Exception", e);
        } finally {
            try {
                conn.setAutoCommit(oldCommit);
                DBUtils.releaseConn(conn, ps, rs);
            } catch (SQLException e) {
                log.error("updateRankYesterdayAndBefore releaseConn thorws", e);
            }
        }
    }
}
